package com.app.util

import android.annotation.SuppressLint
import android.content.Context
import android.database.Cursor
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import com.app.model.News
import com.app.model.User
import org.litepal.LitePal
import java.util.ArrayList


class DBOpenHelper internal constructor(context: Context?) :
    SQLiteOpenHelper(context, "db_test", null, 2) {

    /**
     * 声明一个AndroidSDK自带的数据库变量db
     */
    private val db: SQLiteDatabase

    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(
            "CREATE TABLE IF NOT EXISTS user(" +
                    "_id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "name TEXT," +
                    "password TEXT," +
                    "email TEXT," +
                    "phonenum TEXT)"
        )
        db.execSQL(
            "CREATE TABLE IF NOT EXISTS shoucang(" +
                    "user_name TEXT," +
                    "news_id BIGINT)"
        )
        db.execSQL(
            "CREATE TABLE IF NOT EXISTS history(" +
                    "user_name TEXT," +
                    "news_id BIGINT)"
        )
    }

    fun getMySearch(input: String): List<News> {
        val input1 = "%" + input + "%"
        val list = LitePal.where("title like ? or category like ? or author_name like ?",
                                        input1, input1,input1).find(News::class.java)
        return list
    }

    fun addCollect(user: String, newsId: Long) {
        db.execSQL("INSERT INTO shoucang (user_name, news_id) VALUES(?,?)",
        arrayOf<Any>(user, newsId))
    }

    fun deleteCollect(user: String, newsId: Long) {
        db.execSQL("DELETE FROM shoucang WHERE user_name=? AND news_id=?", arrayOf<Any>(user, newsId))
//        "模拟取消了收藏".showToast()
    }

    fun isCollected(user: String, newsId: Long): Boolean {
//        val rsl = db.execSQL("SELECT * FROM collect WHERE user_name= AND news_id= $user$newsId")
        val cursor = db.rawQuery("SELECT * FROM shoucang WHERE user_name=? AND news_id=?", arrayOf<String>(user, newsId.toString()))
        if(cursor.getCount() == 0) {
            cursor.close()
            return false
        }
        cursor.close()
        return true
    }

    fun getMyCollecions(user: String): List<News> {
        var newsList: List<News> = listOf()
        val cursor = db.rawQuery("SELECT news_id FROM shoucang WHERE user_name=?", arrayOf<String>(user))
        if (cursor.moveToLast()) {
            do{
                val newsId = cursor.getLong(0);
                val tmpList = LitePal.where("id=?", newsId.toString()).find(News::class.java)
                newsList = listOf(newsList, tmpList).flatten()
            }while(cursor.moveToPrevious());
        }
        return newsList
    }

    //浏览历史
    fun addHistory(user: String, newsId: Long) {
        db.execSQL("INSERT INTO history (user_name, news_id) VALUES(?,?)",
            arrayOf<Any>(user, newsId))
    }
    fun deleteHistory() {
        db.execSQL("DELETE FROM history")
    }
    fun isHised(user: String, newsId: Long): Boolean {
        val cursor = db.rawQuery("SELECT * FROM history WHERE user_name=? AND news_id=?", arrayOf<String>(user, newsId.toString()))
        if(cursor.getCount() == 0) {
            cursor.close()
            return false
        }
        cursor.close()
        return true
    }
    fun getMyHistory(user: String):List<News>{
        var hisList: List<News> = listOf()
        val cursor = db.rawQuery("SELECT news_id FROM history WHERE user_name=?", arrayOf<String>(user))
        if (cursor.moveToLast()) {
            do{
                val newsId = cursor.getLong(0);
                val tmpList = LitePal.where("id=?", newsId.toString()).find(News::class.java)
                hisList = listOf(hisList, tmpList).flatten()
            }while(cursor.moveToPrevious());
        }
        return hisList
    }

    //版本适应
    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL("DROP TABLE IF EXISTS user")
        onCreate(db)
    }

    fun add(name: String, password: String, email: String, phonenum: String) {
        db.execSQL(
            "INSERT INTO user (name,password,email,phonenum) VALUES(?,?,?,?)",
            arrayOf<Any>(name, password, email, phonenum)
        )
    }

    fun delete(name: String, password: String) {
        db.execSQL("DELETE FROM user WHERE name = AND password =$name$password")
    }

    fun updata(password: String) {
        db.execSQL("UPDATE user SET password = ?", arrayOf<Any>(password))
    }


    val allData: ArrayList<User>
        get() {
            val list: ArrayList<User> = ArrayList<User>()
            @SuppressLint("Recycle") val cursor =
                db.query("user", null, null, null, null, null, "name DESC")
            while (cursor.moveToNext()) {
                val name = cursor.getString(cursor.getColumnIndex("name"))
                val email = cursor.getString(cursor.getColumnIndex("email"))
                val phonenum = cursor.getString(cursor.getColumnIndex("phonenum"))
                val password = cursor.getString(cursor.getColumnIndex("password"))
                list.add(User(name, password, email, phonenum))
            }
            return list
        }
    init {
        db = readableDatabase
    }
}

